from itertools import groupby

from odoo import api,fields, models
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError


class FinishedProductReport(models.Model):
    _name = 's.finished.product.report'
    _description = "Finished Product Report"  # 投料报表
    _auto = False

    base_mo = fields.Char(string='Baseline Manufacturing Order')
    mo_type = fields.Selection([
        ('N', 'batch'),
        ('S', 'sample'),
        ('M', 'feed')
    ], string='Production Type')
    product_id = fields.Many2one('product.product', string='Product')
    product_code = fields.Char(related='product_id.default_code', string='Product Code')
    product_qty = fields.Float('qty(PCS)')
    panel_qty = fields.Float(string='PNL Quantity')
    yield_rate = fields.Float('yield rate')
    pnl_meters = fields.Float(string='PNL Meters', digits=(16, 1))
    product_create_date = fields.Datetime('Product Create Date')
    commitment_date = fields.Datetime('Commitment Date', compute='_compute_commitment_date')

    # def _compute_yield_rate(self):
    #     for rec in self:
    #         production_ids = self.env['mrp.production'].search([
    #             ('base_mo', '=', rec.base_mo),
    #             ('mo_type', '=', 'N'),
    #             ('product_id.categ_id.code', '=', 'CHP')
    #         ])
    #         sale_line_ids = production_ids.mapped('s_sale_order_line_ids')
    #         development_line_ids = production_ids.mapped('s_development_line_ids')
    #         order_qty = sum(sale_line_ids.mapped('product_uom_qty')) + sum(development_line_ids.mapped('product_qty'))
    #         rec.yield_rate =  order_qty / rec.product_qty

    @api.depends('base_mo', 'product_id')
    def _compute_commitment_date(self):
        for rec in self:
            mrp_id = self.env['mrp.production'].search([
                ('base_mo', '=', rec.base_mo),
                ('product_id', '=', rec.product_id.id)
            ], limit=1, order="create_date")
            if mrp_id and mrp_id.s_sale_order_line_ids and mrp_id.s_sale_order_line_ids[0].order_id:
                rec.commitment_date = mrp_id.s_sale_order_line_ids[0].order_id.commitment_date
            else:
                rec.commitment_date = None

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        product_id = self._context.get('product_id')
        production_type = self._context.get('production_type', '')
        where = ''
        where_list = ["mp.state != 'cancel'", "pc.code='CHP' ", " mp.active = true ", " mp.mo_type != 'W'"]
        if start_date:
            where_list.append(f"mp.s_feeding_date >= '{start_date}'")

        if end_date:
            where_list.append(f"mp.s_feeding_date <= '{end_date}'")

        if product_id:
            where_list.append(f' mp.product_id = {product_id}')
        if production_type and production_type != 'all':
            if production_type == 'batch':
                where_list.append(" pt.materiel_status = 'P' and mp.mo_type = 'N' ")
            elif production_type == 'sample':
                where_list.append(" pt.materiel_status != 'P' and mp.mo_type = 'N' ")
            elif production_type == 'feed':
                where_list.append(" mp.mo_type = 'M'")
        if where_list:
            where = 'where ' + ' and '.join(where_list)
        select_sql = f"""
            SELECT
                ROW_NUMBER() OVER () AS id,
                mp.base_mo,
                case when mp.mo_type = 'N' and pt.materiel_status = 'P' then 'N'
                     when mp.mo_type = 'N' and pt.materiel_status != 'P' then 'S'
                     else 'M' end mo_type,
                mp.product_id,
                sum(mp.panel_uom_qty) as panel_qty,
                sum(mp.panel_uom_qty * uom.uom_value * uom2.uom_value / 1000000 ) as pnl_meters,
                min(mp.s_feeding_date) as product_create_date,
                sum(mp.product_qty) AS product_qty,
                (sum(mp.product_qty) - sum(mp.qty_reject)) / sum(mp.product_qty) yield_rate
                
            FROM
                mrp_production mp
                LEFT JOIN (
                    SELECT
                        mpuv.uom_value,
                        mpuv.production_id 
                    FROM
                        mrp_production_uom_value mpuv
                        LEFT JOIN uom_uom uu ON uu.ID = mpuv.uom_id 
                    WHERE uu.code ='PNLL'
                ) AS uom ON uom.production_id = mp.ID 
                
                LEFT JOIN (
                    SELECT
                        mpuv.uom_value,
                        mpuv.production_id 
                    FROM
                        mrp_production_uom_value mpuv
                        LEFT JOIN uom_uom uu ON uu.ID = mpuv.uom_id 
                    WHERE
                        uu.code ='PNLW'
                ) AS uom2 ON uom2.production_id = mp.ID 
                
                left join product_product pp on mp.product_id=pp.id
                left join product_template pt on pp.product_tmpl_id=pt.id
                left join product_category pc on pc.id = pt.categ_id
                
            {where}
            group by mp.base_mo,mp.mo_type,product_id,pt.materiel_status
        """
        return select_sql
